/****** Object:  StoredProcedure [dbo].[SP_TERAZI_URUN_DAT]    Script Date: 23.04.2018 16:56:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SP_TERAZI_URUN_DAT] 
	@STOKIDS		VARCHAR(MAX),
	@ILKTARIH		SMALLDATETIME,
	@SONTARIH		SMALLDATETIME,
	@FIYATTIPID		INT,
	@ISFIYATDEGISEN	BIT
AS
SELECT 
	'1'+
	dbo.ZL(CONVERT(VARCHAR(MAX), TSM.PLUNO),6)+
	dbo.SR(CONVERT(VARCHAR(MAX), TSB.BARKOD),20)+
	dbo.SR(CONVERT(VARCHAR(MAX), TSM.KISAADI),20)+
	dbo.ZL(CONVERT(FLOAT, ROUND(ISNULL(ISNULL(TSFS.FIYAT, 0),0)*100.00,0)),9)+
	dbo.ZL(CONVERT(VARCHAR(MAX), TSM.DEPTNO),2)+
	dbo.SR(CONVERT(VARCHAR(MAX), TB.BIRIM),4)+
	TSM.TARTILI AS DATA
FROM TBL_STOK_MAIN TSM WITH (NOLOCK)
	INNER JOIN TBL_STOK_BARKOD TSB WITH (NOLOCK) ON TSB.STOKID = TSM.ID
	INNER JOIN TBL_BIRIM TB WITH (NOLOCK) ON TB.ID = TSM.BIRIMID
	INNER JOIN TBL_STOK_GRUP TSG WITH (NOLOCK) ON TSG.ID = TSM.GRUPID
	LEFT JOIN (
				SELECT 
				STOKID,
				FIYAT,
				CREATEDATE,
				CHANGEDATE
				FROM TBL_STOK_FIYAT WITH (NOLOCK)
				WHERE FIYATTIPID IN (SELECT ID FROM TBL_FIYAT_TIP WHERE ALISSATIS = 'S' AND GECERLIFIYAT = 'E')
	) TSFS ON TSFS.STOKID = TSM.ID

